package pl.edu.pw.perg.compinfodb.xls.from;

import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CN_INPUT_INDEX_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CN_NAME_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CN_OUTPUT_INDEX_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CN_TYPE_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CONNECTIONS_SHEET;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CONNECTOR_NAMES_SHEET;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CON_CBL_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CON_IN_CON_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CON_IN_ELT_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CON_OUT_CON_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.CON_OUT_ELT_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.ELT_ATTRIBUTES_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.ELT_ID_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.ELT_LABEL_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.ELT_TYPE_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.FIELDS_SHEET;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.FIELD_FIELD_NAME_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.HEADER_ROWS;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.HISTORY_SHEET;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.HIST_AUTHOR_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.HIST_COMMENT_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.HIST_DATE_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.HIST_ELEMENT_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.HIST_MODULE_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.LOCATION_NOT_AVAILABLE;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.MODULES_SHEET;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.STRUCTURE_SHEET;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.STRUCT_ID_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.STRUCT_LOCATION_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.STRUCT_PARENT_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.STRUCT_TYPE_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPES_SHEET;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPE_CABLE_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPE_ELEMENT_TYPE_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPE_FIELDS_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPE_FIRST_INDEX_IN_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPE_FIRST_INDEX_OUT_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPE_INPUTS_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPE_OUTPUTS_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.TYPE_PARENT_COL;
import static pl.edu.pw.perg.compinfodb.xls.XLSConstants.XLS_FILE_IMPORT;
import static pl.edu.pw.perg.compinfodb.xls.XLSUtils.getColumnNr;
import static pl.edu.pw.perg.compinfodb.xls.XLSUtils.getIntegerProperty;
import static pl.edu.pw.perg.compinfodb.xls.XLSUtils.getProperty;

import java.io.File;
import java.io.IOException;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import pl.edu.pw.perg.compinfodb.entities.Connector;
import pl.edu.pw.perg.compinfodb.entities.ConnectorName;
import pl.edu.pw.perg.compinfodb.entities.ElementType;
import pl.edu.pw.perg.compinfodb.entities.Field;
import pl.edu.pw.perg.compinfodb.entities.HistoryItem;
import pl.edu.pw.perg.compinfodb.entities.InternalConnection;
import pl.edu.pw.perg.compinfodb.entities.ModuleHistoryItem;
import pl.edu.pw.perg.compinfodb.entities.PhysicalElement;
import pl.edu.pw.perg.compinfodb.entities.StructureHistoryItem;
import pl.edu.pw.perg.compinfodb.entities.VirtualElement;
import pl.edu.pw.perg.compinfodb.xls.XLSConstants;

public class XLSMetadataReader {

	private static int IC_OUT_COL_NR;
	private static int IC_IN_COL_NR;
	private static int IC_TYPE_COL_NR;
	private static int E_IDENTIFIER_COL_NR;
	private static int E_LABEL_COL_NR;
	private static int E_TYPE_COL_NR;
	private static int C_IN_CON_COL_NR;
	private static int C_OUT_CON_COL_NR;
	private static int C_IN_EL_COL_NR;
	private static int C_OUT_EL_COL_NR;
	private static int C_CABLE_COL_NR;
	private static int S_LOCATION_COL_NR;
	private static int S_PARENT_COL_NR;
	private static int S_TYPE_COL_NR;
	private static int S_NAME_COL_NR;
	private static int T_FIELDS_COL_NR;
	private static int T_IN_CON_COL_NR;
	private static int T_OUT_CON_COL_NR;
	private static int T_PARENT_COL_NR;
	private static int T_NAME_COL_NR;
	private static int T_IN_INDEXING_START_FROM_COL_NR;
	private static int T_OUT_INDEXING_START_FROM_COL_NR;
	private static int T_CABLE_FLAG_COL_NR;
	private static int F_NAME_COL_NR;
	private static int CN_TYPE_COL_NR;
	private static int CN_INPUT_INDEX_COL_NR;
	private static int CN_OUTPUT_INDEX_COL_NR;
	private static int CN_NAME_COL_NR;

	private Sheet fieldsSheet;
	private Sheet typesSheet;
	private Sheet historySheet;
	private Sheet modulesSheet;
	private Sheet connectionsSheet;
	private Sheet connectorNamesSheet;
	private Sheet elementModulesSheet;
	private Sheet internalConnectionSheet;

	private Workbook workBook;

	private Map<String, Field> fieldMap = new HashMap<String, Field>();
	private Map<String, ElementType> typeMap = new HashMap<String, ElementType>();
	private Map<String, VirtualElement> elementMap = new HashMap<String, VirtualElement>();
	private Map<VirtualElement, PhysicalElement> complexMap = new HashMap<VirtualElement, PhysicalElement>();
	private int H_AUTHOR_COL_NR;
	private int H_MODULE_COL_NR;
	private int H_ELEMENT_COL_NR;
	private int H_COMMENT_COL_NR;
	private int H_DATE_COL_NR;
	private int E_ATTRIBUTES_COL_NR;
	private int HEADER_ROWS_NR;
	private static final Logger logger = LoggerFactory
			.getLogger(XLSMetadataReader.class);

	public XLSMetadataReader() {
		try {
			workBook = Workbook.getWorkbook(new File(
					getProperty(XLS_FILE_IMPORT)));
		} catch (BiffException e) {
			logger.error("Cannot load spreadsheets!", e);
			System.exit(1);
			e.printStackTrace();
		} catch (IOException e) {
			logger.error("Cannot load spreadsheets!");
			System.exit(1);
		}

		fieldsSheet = getSheet(getProperty(FIELDS_SHEET));
		typesSheet = getSheet(getProperty(TYPES_SHEET));
		historySheet = getSheet(getProperty(HISTORY_SHEET));
		modulesSheet = getSheet(getProperty(STRUCTURE_SHEET));
		elementModulesSheet = getSheet(getProperty(MODULES_SHEET));
		connectionsSheet = getSheet(getProperty(CONNECTIONS_SHEET));
		connectorNamesSheet = getSheet(getProperty(CONNECTOR_NAMES_SHEET));

		try {

			E_IDENTIFIER_COL_NR = getColumnNr(ELT_ID_COL);
			E_LABEL_COL_NR = getColumnNr(ELT_LABEL_COL);
			E_TYPE_COL_NR = getColumnNr(ELT_TYPE_COL);
			C_IN_CON_COL_NR = getColumnNr(CON_IN_CON_COL);
			C_OUT_CON_COL_NR = getColumnNr(CON_OUT_CON_COL);
			C_IN_EL_COL_NR = getColumnNr(CON_IN_ELT_COL);
			C_OUT_EL_COL_NR = getColumnNr(CON_OUT_ELT_COL);
			C_CABLE_COL_NR = getColumnNr(CON_CBL_COL);
			S_LOCATION_COL_NR = getColumnNr(STRUCT_LOCATION_COL);
			S_PARENT_COL_NR = getColumnNr(STRUCT_PARENT_COL);
			S_TYPE_COL_NR = getColumnNr(STRUCT_TYPE_COL);
			S_NAME_COL_NR = getColumnNr(STRUCT_ID_COL);
			T_IN_INDEXING_START_FROM_COL_NR = getColumnNr(TYPE_FIRST_INDEX_IN_COL);
			T_OUT_INDEXING_START_FROM_COL_NR = getColumnNr(TYPE_FIRST_INDEX_OUT_COL);
			T_FIELDS_COL_NR = getColumnNr(TYPE_FIELDS_COL);
			T_IN_CON_COL_NR = getColumnNr(TYPE_INPUTS_COL);
			T_OUT_CON_COL_NR = getColumnNr(TYPE_OUTPUTS_COL);
			T_CABLE_FLAG_COL_NR = getColumnNr(TYPE_CABLE_COL);
			T_PARENT_COL_NR = getColumnNr(TYPE_PARENT_COL);
			T_NAME_COL_NR = getColumnNr(TYPE_ELEMENT_TYPE_COL);
			F_NAME_COL_NR = getColumnNr(FIELD_FIELD_NAME_COL);
			HEADER_ROWS_NR = getIntegerProperty(HEADER_ROWS);
			H_AUTHOR_COL_NR = getColumnNr(HIST_AUTHOR_COL);
			H_MODULE_COL_NR = getColumnNr(HIST_MODULE_COL);
			H_ELEMENT_COL_NR = getColumnNr(HIST_ELEMENT_COL);
			H_DATE_COL_NR = getColumnNr(HIST_DATE_COL);
			H_COMMENT_COL_NR = getColumnNr(HIST_COMMENT_COL);
			E_ATTRIBUTES_COL_NR = getColumnNr(ELT_ATTRIBUTES_COL);
			CN_TYPE_COL_NR = getColumnNr(CN_TYPE_COL);
			CN_INPUT_INDEX_COL_NR = getColumnNr(CN_INPUT_INDEX_COL);
			CN_OUTPUT_INDEX_COL_NR = getColumnNr(CN_OUTPUT_INDEX_COL);
			CN_NAME_COL_NR = getColumnNr(CN_NAME_COL);

		} catch (NumberFormatException e) {
			logger
					.error(
							"One of numeric properties is not a number. Check properties file!",
							e);
			System.exit(1);
		} catch (InvalidMetadataFormatException e) {
			handleFormatError(e);
			System.exit(1);
		}

	}

	public List<Field> getFields() throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(fieldsSheet.getName());
		if (fieldsSheet == null) {
			handleFormatError("Sheet not found! Check sheets names. ", sheet);
			return null;
		}
		List<Field> fields = new ArrayList<Field>();
		for (int i = HEADER_ROWS_NR; i < fieldsSheet.getRows(); i++) {
			String name = fieldsSheet.getCell(F_NAME_COL_NR, i).getContents();
			if (name.equals(""))
				handleFormatError("Field name cannot be empty!", sheet,
						F_NAME_COL_NR, i);

			Field field = new Field();
			field.setFieldName(name);
			if (fieldMap.containsKey(name)) {
				handleFormatError("Duplicate definition of field: " + name,
						sheet, F_NAME_COL_NR, i);
				continue;
			}
			fieldMap.put(name, field);
			fields.add(field);
		}
		return fields;
	}

	private void handleFormatError(String message, XLSSheets sheet, int col,
			int row) {
		handleFormatError(new InvalidMetadataFormatException(message, sheet,
				col, row));
	}

	private void handleFormatError(String message, XLSSheets sheet) {
		handleFormatError(new InvalidMetadataFormatException(message, sheet));

	}

	public List<ElementType> getTypes() throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(typesSheet.getName());
		Map<ElementType, String> parents = new HashMap<ElementType, String>();
		if (typesSheet == null) {
			handleFormatError("Sheet not found! Check sheets names. ", sheet);
			return null;
		}
		List<ElementType> types = new ArrayList<ElementType>();
		for (int i = HEADER_ROWS_NR; i < typesSheet.getRows(); i++) {
			String name = typesSheet.getCell(T_NAME_COL_NR, i).getContents()
					.trim();
			String parentName = typesSheet.getCell(T_PARENT_COL_NR, i)
					.getContents().trim();
			String outConnNr = typesSheet.getCell(T_OUT_CON_COL_NR, i)
					.getContents();
			if (name.equals("") && parentName.equals("")
					&& outConnNr.equals(""))
				continue;
			String indexingInStartFrom = typesSheet.getCell(
					T_IN_INDEXING_START_FROM_COL_NR, i).getContents().trim();
			String indexingOutStartFrom = typesSheet.getCell(
					T_OUT_INDEXING_START_FROM_COL_NR, i).getContents().trim();
			String cableFlag = typesSheet.getCell(T_CABLE_FLAG_COL_NR, i)
					.getContents().trim();
			ElementType type = new ElementType();
			if (name.equals(""))
				handleFormatError("Type name cannot be empty!", sheet,
						T_NAME_COL_NR, i);
			type.setTypeName(name);
			if (typeMap.containsKey(name)) {
				handleFormatError("Duplicate definition of type: " + name,
						sheet, T_NAME_COL_NR, i);
				continue;
			}
			typeMap.put(name, type);
			parents.put(type, parentName);
			// if (!isTransparent.equals("")
			// && !isTransparent.equals(TRANSPARENT_TRUE))
			// handleFormatError(
			// "Transparent flag should be " + TRANSPARENT_TRUE
			// + " or empty", sheet, String.valueOf(
			// convertIntToChar(T_TRANSPARENT_COL_NR))
			// .toUpperCase(), i);
			// type.setTransparent(isTransparent.equals(TRANSPARENT_TRUE);

			if (!cableFlag.equals("0") && !cableFlag.equals("1")
					&& !cableFlag.equals(""))
				handleFormatError("Cable flag should be empty, " + 0 + " or "
						+ 1, sheet, T_CABLE_FLAG_COL_NR, i);
			type.setCable(cableFlag.equals("1"));

			String inConnNr = typesSheet.getCell(T_IN_CON_COL_NR, i)
					.getContents();
			if (!inConnNr.equals(""))
				try {
					type.setInputConnectorsNr(Integer.parseInt(inConnNr));
				} catch (NumberFormatException e) {
					handleFormatError(
							"Number of input connectors should be an integer number!",
							sheet, T_IN_CON_COL_NR, i);
				}
			if (!outConnNr.equals(""))
				type.setOutputConnectorsNr(Integer.parseInt(outConnNr));

			Set<Field> fields = new HashSet<Field>();
			for (int j = T_FIELDS_COL_NR; j < typesSheet.getColumns(); j++) {
				String fieldName = typesSheet.getCell(j, i).getContents();
				if (fieldName.equals(""))
					break;
				Field field = fieldMap.get(fieldName);
				if (field == null)
					handleFormatError("Field named " + fieldName
							+ " not found.", sheet, j, i);
				fields.add(field);
			}
			type.setFields(fields);
			types.add(type);
			int indexingStartValueIn = -1;

			if (indexingInStartFrom.equals("")) {
				handleFormatError("Indexing start value is required.", sheet,
						T_IN_INDEXING_START_FROM_COL_NR, i);
			}
			if (!indexingInStartFrom.equals("")) {
				try {
					indexingStartValueIn = Integer
							.parseInt(indexingInStartFrom);
				} catch (NumberFormatException e) {
					handleFormatError("Indexing start value should"
							+ " be an integer.\nWas: " + indexingInStartFrom,
							sheet, T_IN_INDEXING_START_FROM_COL_NR, i);
				}
			}
			// startIndexingMap.put(type, indexingStartValue);
			type.setFirstIndexIn(indexingStartValueIn);

			int indexingStartValueOut = -1;

			if (indexingOutStartFrom.equals("")) {
				handleFormatError("Indexing start value is required.", sheet,
						T_OUT_INDEXING_START_FROM_COL_NR, i);
			}
			if (!indexingOutStartFrom.equals("")) {
				try {
					indexingStartValueOut = Integer
							.parseInt(indexingOutStartFrom);
				} catch (NumberFormatException e) {
					handleFormatError("Indexing start value should"
							+ " be an integer.\nWas: " + indexingOutStartFrom,
							sheet, T_OUT_INDEXING_START_FROM_COL_NR, i);
				}
			}
			// startIndexingMap.put(type, indexingStartValue);
			type.setFirstIndexOut(indexingStartValueOut);
		}

		for (ElementType elementType : types) {
			String parentName = parents.get(elementType);
			elementType.setParentType(typeMap.get(parentName));
		}
		// connectInternally();
		return types;
	}

	protected List<VirtualElement> getElementsStructure()
			throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(modulesSheet.getName());
		if (sheet == null) {
			handleFormatError("Sheet not found! Check sheets names. ", sheet);
			return null;
		}
		Map<VirtualElement, String> parents = new HashMap<VirtualElement, String>();
		Map<VirtualElement, Integer> rows = new HashMap<VirtualElement, Integer>();
		List<VirtualElement> elements = new ArrayList<VirtualElement>();
		for (int i = HEADER_ROWS_NR; i < modulesSheet.getRows(); i++) {
			String name = modulesSheet.getCell(S_NAME_COL_NR, i).getContents()
					.trim();
			String typeName = modulesSheet.getCell(S_TYPE_COL_NR, i)
					.getContents().trim();
			String parentName = modulesSheet.getCell(S_PARENT_COL_NR, i)
					.getContents().trim();
			if (name.equals("") && typeName.equals("") && parentName.equals(""))
				continue;
			if (name.equals("")) {
				handleFormatError("Element identifier cannot be empty!", sheet,
						S_NAME_COL_NR, i);
			}
			if (typeName.equals(""))
				handleFormatError("Element type name cannot be empty!", sheet,
						S_TYPE_COL_NR, i);
			ElementType et = typeMap.get(typeName);
			if (et == null) {
				handleFormatError("Element type named " + typeName
						+ " not found.", sheet, S_TYPE_COL_NR, i);
				continue;
			}
			VirtualElement ve = new VirtualElement(et);
			ve.setName(name);
			parents.put(ve, parentName);
			// rows.add(i - HEADER_ROWS, ve);
			rows.put(ve, Integer.valueOf(i));
			if (elementMap.containsKey(name)) {
				handleFormatError("Duplicate definition of element: " + name,
						sheet, E_IDENTIFIER_COL_NR, i);
				continue;
			}

			// if (cable)
			// cableMap.put(name, ve);
			// else
			elementMap.put(name, ve);

			elements.add(ve);
			int location = LOCATION_NOT_AVAILABLE;
			String locationStr = modulesSheet.getCell(S_LOCATION_COL_NR, i)
					.getContents().trim();

			try {
				if (!locationStr.equals(""))
					location = Integer.parseInt(locationStr);
			} catch (NumberFormatException e) {
				handleFormatError("Position "
						+ "should be an long integer value! Was: "
						+ locationStr, sheet, S_LOCATION_COL_NR, i, e);
			}
			ve.setLocation(location);
		}
		for (VirtualElement virtualElement : elements) {
			String parentName = parents.get(virtualElement);
			int i = rows.get(virtualElement).intValue();
			VirtualElement parent = elementMap.get(parentName);
			if (!parentName.equals("") && parent == null)
				handleFormatError("Element's parent, named " + parentName
						+ " not found.", sheet, S_PARENT_COL_NR, i);
			virtualElement.setParent(parent);
		}
		return elements;
	}

	private void handleFormatError(String message, XLSSheets sheet, int col,
			int row, Throwable e) {
		handleFormatError(new InvalidMetadataFormatException(message, sheet,
				col, row, e));

	}

	public List<Connector> getConnections()
			throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(connectionsSheet.getName());
		if (connectionsSheet == null) {
			handleFormatError("Sheet not found! Check sheets names. ", sheet);
			return null;
		}
		List<Connector> connectors = new ArrayList<Connector>();
		Map<VirtualElement, List<Integer>> inConnectors = new HashMap<VirtualElement, List<Integer>>();
		Map<VirtualElement, List<Integer>> outConnectors = new HashMap<VirtualElement, List<Integer>>();
		Map<VirtualElement, Connector> cablesMap = new HashMap<VirtualElement, Connector>();
		for (int i = HEADER_ROWS_NR; i < connectionsSheet.getRows(); i++) {

			String outElementName = connectionsSheet
					.getCell(C_OUT_EL_COL_NR, i).getContents().trim();
			String inElementName = connectionsSheet.getCell(C_IN_EL_COL_NR, i)
					.getContents().trim();

			String cableName = connectionsSheet.getCell(C_CABLE_COL_NR, i)
					.getContents().trim();

			if (outElementName.equals("") && inElementName.equals("")
					&& cableName.equals(""))
				continue;
			if (
			// outElementName.equals("")
			// || inElementName.equals("")
			// ||
			cableName.equals("")) {
				handleFormatError(
						"Cable, output or input element identifier is missing!",
						sheet, "unspecified", i);
				continue;
			}

			VirtualElement outElt = outElementName.equals("") ? null
					: elementMap.get(outElementName);
			VirtualElement inElt = inElementName.equals("") ? null : elementMap
					.get(inElementName);
			VirtualElement cable = (VirtualElement) elementMap.get(cableName);

			if (cable == null) {
				handleFormatError("Cable " + cableName + " undefined.", sheet,
						C_OUT_EL_COL_NR, i);
				continue;
			}

			int firstIndexOut = 0;
			int firstIndexIn = 0;
			int outConnNr = firstIndexOut;
			int inConnNr = firstIndexIn;

			if (outElt != null) {
				firstIndexOut = outElt.getElementType().getFirstIndexOut();

				try {
					outConnNr = Integer.parseInt(connectionsSheet.getCell(
							C_OUT_CON_COL_NR, i).getContents().trim());
				} catch (NumberFormatException e) {
					handleFormatError(
							"Output connector index "
									+ "should be an long integer value! Count starts with "
									+ firstIndexOut, sheet, C_OUT_CON_COL_NR,
							i, e);
					continue;

				}

				if (outConnNr >= outElt.getElementType()
						.getOutputConnectorsNr()
						+ firstIndexOut
						&& outConnNr < firstIndexOut) {
					handleFormatError("Output connector index: " + outConnNr
							+ " should be between " + firstIndexOut
							+ " and type's: " + outElt.getElementType()
							+ " Output Connectors Nr: "
							+ outElt.getElementType().getOutputConnectorsNr()
							+ ". Check if both values are correct.", sheet,
							C_OUT_CON_COL_NR, i);
					continue;
				}

				if (outConnectors.containsKey(outElt)) {
					List<Integer> eltConnectors = outConnectors.get(outElt);
					if (eltConnectors.contains(outConnNr)) {
						handleFormatError("Duplicate input connector "
								+ outConnNr + " for element "
								+ outElt.getName(), sheet,
								CN_OUTPUT_INDEX_COL_NR, i);
					} else
						eltConnectors.add(outConnNr);
				} else {
					List<Integer> eltConnectors = new LinkedList<Integer>();
					eltConnectors.add(outConnNr);
					outConnectors.put(outElt, eltConnectors);
				}
			}

			if (inElt != null) {
				firstIndexIn = inElt.getElementType().getFirstIndexIn();
				try {
					inConnNr = Integer.parseInt(connectionsSheet.getCell(
							C_IN_CON_COL_NR, i).getContents().trim());
				} catch (NumberFormatException e) {
					handleFormatError(
							"Input connector index "
									+ "should be an long integer value! Count starts with "
									+ firstIndexOut, sheet, C_IN_CON_COL_NR, i,
							e);
					continue;

				}

				if (inConnNr >= inElt.getElementType().getInputConnectorsNr()
						+ firstIndexOut
						&& inConnNr < firstIndexIn) {
					handleFormatError("Input connector index: " + inConnNr
							+ "\n" + "should be between " + firstIndexIn
							+ " and type's: " + inElt.getElementType()
							+ " Input Connectors Nr: "
							+ inElt.getElementType().getInputConnectorsNr()
							+ "\nCheck if both values are correct.", sheet,
							C_IN_CON_COL_NR, i);
					continue;
				}

				if (inConnectors.containsKey(inElt)) {
					List<Integer> eltConnectors = inConnectors.get(inElt);
					if (eltConnectors.contains(inConnNr)) {
						handleFormatError("Duplicate input connector "
								+ inConnNr + " for element " + inElt.getName(),
								sheet, C_IN_CON_COL_NR, i);
					} else
						eltConnectors.add(inConnNr);
				} else {
					List<Integer> eltConnectors = new LinkedList<Integer>();
					eltConnectors.add(inConnNr);
					inConnectors.put(inElt, eltConnectors);
				}
			}

			Connector c = new Connector();
			if (cablesMap.containsKey(cable)){
				Connector cn = cablesMap.get(cable);
				handleFormatError("Cable already used for connection : [" + 
						cn.getElementIn() + " - " + cn.getElementOut() + "]"  , XLSSheets.CONNECTIONS, C_CABLE_COL_NR, i);
			}
			else {
				cablesMap.put(cable, c);
				c.setCable(cable);
				c.setElementIn(outElt);
				c.setElementOut(inElt);
				c.setPositionIn(outConnNr);
				c.setPositionOut(inConnNr);
				if (outElt != null)
					outElt.getOutputConnectors().add(c);
				if (inElt != null)
					inElt.getInputConnectors().add(c);
				connectors.add(c);
			}
		}

		return connectors;
	}

	private void handleFormatError(String message, XLSSheets sheet, String col,
			int row) {
		handleFormatError(new InvalidMetadataFormatException(message, sheet,
				col, row));

	}

	public List<HistoryItem> getHistory() {
		XLSSheets sheet = XLSSheets.findSheet(historySheet.getName());
		if (sheet == null) {
			handleFormatError("Sheet not found! Check sheets names. ", sheet);
			return null;
		}
		List<HistoryItem> history = new LinkedList<HistoryItem>();
		for (int i = HEADER_ROWS_NR; i < historySheet.getRows(); i++) {
			String author = historySheet.getCell(H_AUTHOR_COL_NR, i)
					.getContents();
			String comment = historySheet.getCell(H_COMMENT_COL_NR, i)
					.getContents();
			String date = historySheet.getCell(H_DATE_COL_NR, i).getContents();
			String element = historySheet.getCell(H_ELEMENT_COL_NR, i)
					.getContents();
			String module = historySheet.getCell(H_MODULE_COL_NR, i)
					.getContents();
			if (element.equals("") && module.equals("")) {
				handleFormatError("Module or element is required!", sheet,
						H_ELEMENT_COL_NR, i);
				continue;
			}
			HistoryItem event;
			if (!element.equals(""))
				event = new StructureHistoryItem();
			else
				event = new ModuleHistoryItem();
			event.setAuthor(author);
			try {
				event.setCreationDate(XLSConstants.dateFormat.parse(date));
			} catch (ParseException e) {
				handleFormatError("Wrong date format: " + date, sheet,
						H_DATE_COL_NR, i);
				continue;
			}
			event.setDescription(comment);
			history.add(event);
		}
		return history;
	}

	private List<PhysicalElement> getPhysicalElements(Sheet physicalSheet)
			throws InvalidMetadataFormatException {
		XLSSheets sheet = XLSSheets.findSheet(physicalSheet.getName());
		if (sheet == null) {
			handleFormatError("Sheet not found! Check sheets names. ", sheet);
			return null;
		}
		List<PhysicalElement> phElements = new ArrayList<PhysicalElement>();
		for (int i = HEADER_ROWS_NR; i < physicalSheet.getRows(); i++) {
			String elementTypeName = physicalSheet.getCell(E_TYPE_COL_NR, i)
					.getContents().trim();
			String label = physicalSheet.getCell(E_LABEL_COL_NR, i)
					.getContents().trim();
			String virtualElementName = physicalSheet.getCell(
					E_IDENTIFIER_COL_NR, i).getContents().trim();
			if (elementTypeName.equals("") && label.equals("")
					&& virtualElementName.equals(""))
				continue;
			if (elementTypeName.equals(""))
				handleFormatError("Element type " + "cannot be null.", sheet,
						E_TYPE_COL_NR, i);
			PhysicalElement pe = new PhysicalElement();
			ElementType et = typeMap.get(elementTypeName);
			if (et == null)
				handleFormatError("Element type " + elementTypeName
						+ " undefined.", sheet, E_TYPE_COL_NR, i);
			pe.setElementType(et);
			if (label.equals(""))
				handleFormatError("Element label " + "cannot be null.", sheet,
						E_LABEL_COL_NR, i);
			pe.setLabel(label);
			VirtualElement ve = elementMap.get(virtualElementName);
			if (ve != null) {
				pe.setVirtualElement(ve);
				ve.setPhysicalElement(pe);
				complexMap.put(ve, pe);
			}
			phElements.add(pe);
			Set<Field> fields = pe.getElementType().getFields();
			for (int j = E_ATTRIBUTES_COL_NR; j < physicalSheet.getColumns(); j++) {
				String value = physicalSheet.getCell(j, i).getContents();
				if (value.equals(""))
					break;
				String[] pair = value.split(":");
				if (pair.length != 2)
					handleFormatError("Wrong attribute: " + value, sheet, j, i);
				boolean fieldExists = false;
				for (Field field : fields) {
					if (field.getFieldName().equals(pair[0])) {
						fieldExists = true;
						break;
					}
				}
				if (fieldExists)
					pe.setAttribute(pair[0], pair[1]);
				else
					handleFormatError("Field: " + value + " not exists", sheet,
							j, i);
			}
		}
		return phElements;
	}

	public List<ConnectorName> getConnectorNames() {

		XLSSheets sheet = XLSSheets.findSheet(connectorNamesSheet.getName());
		if (sheet == null) {
			handleFormatError("Sheet not found! Check sheets names. ", sheet);
			return null;
		}
		List<ConnectorName> names = new LinkedList<ConnectorName>();
		for (int i = HEADER_ROWS_NR; i < connectorNamesSheet.getRows(); i++) {
			String type = connectorNamesSheet.getCell(CN_TYPE_COL_NR, i)
					.getContents();
			String inputIndex = connectorNamesSheet.getCell(
					CN_INPUT_INDEX_COL_NR, i).getContents();
			String outputIndex = connectorNamesSheet.getCell(
					CN_OUTPUT_INDEX_COL_NR, i).getContents();
			String name = connectorNamesSheet.getCell(CN_NAME_COL_NR, i)
					.getContents();

			if (type.equals("") || name.equals("")) {
				handleFormatError("Type and name are required!", sheet,
						CN_TYPE_COL_NR, i);
				continue;
			}
			ConnectorName cn = new ConnectorName();
			cn.setType(typeMap.get(type));
			cn.setName(name);

			if (inputIndex.equals("") && outputIndex.equals("")
					|| !inputIndex.equals("") && !outputIndex.equals("")) {
				handleFormatError(
						"Exactly one index, input or output, can be specified!",
						sheet, CN_INPUT_INDEX_COL_NR, i);
				continue;
			}
			if (!inputIndex.equals("")) {
				cn.setIo(true);
				cn.setConnectorIndex(Integer.valueOf(inputIndex));
			} else {
				cn.setIo(false);
				cn.setConnectorIndex(Integer.valueOf(outputIndex));
			}
			// cn.setConnectorIndex(inputIndex);
			names.add(cn);
		}

		return names;
	}

	public List<ElementType> connectInternally() {
		List<ElementType> connected = new LinkedList<ElementType>();
		XLSSheets sheet = XLSSheets
				.findSheet(internalConnectionSheet.getName());
		if (internalConnectionSheet == null) {
			handleFormatError("Sheet not found! Check sheets names. ", sheet);
			return connected;
		}

		// odczytaj typ elementu
		for (int i = HEADER_ROWS_NR; i < internalConnectionSheet.getRows(); i++) {
			String elementName = internalConnectionSheet.getCell(
					IC_TYPE_COL_NR, i).getContents().trim();
			if (elementName.equals(""))
				continue;
			ElementType et = typeMap.get(elementName);
			if (et == null) {
				handleFormatError("Undefined type: " + elementName, sheet,
						IC_TYPE_COL_NR, i);
				continue;
			}
			InternalConnection ic = new InternalConnection();
			int positionIn;
			try {
				positionIn = Integer.parseInt(internalConnectionSheet.getCell(
						IC_IN_COL_NR, i).getContents().trim());
			} catch (NumberFormatException e) {
				handleFormatError(
						"Input connector index in Internal Connection should be an integer number!",
						sheet, IC_IN_COL_NR, i);
				continue;
			}
			int positionOut;
			try {
				positionOut = Integer.parseInt(internalConnectionSheet.getCell(
						IC_OUT_COL_NR, i).getContents().trim());
			} catch (NumberFormatException e) {
				handleFormatError(
						"Output connector index in Internal Connection should be an integer number!",
						sheet, IC_OUT_COL_NR, i);
				continue;
			}
			ic.setPositionIn(positionIn);
			ic.setPositionOut(positionOut);
			ic.setType(et);
			et.getInternalConnections().add(ic);
			connected.add(et);
		}
		return connected;
	}

	protected void handleFormatError(InvalidMetadataFormatException e) {
		throw e;
	}

	public List<PhysicalElement> getPhysicalElements() {
		List<PhysicalElement> elements = getPhysicalElements(elementModulesSheet);
		// elements.addAll(getPhysicalElements(elementCablesSheet);
		return elements;
	}

	// public List<ComplexElement> getComplexElements() {
	// List<ComplexElement> complexList = new ArrayList<ComplexElement>();
	// for (VirtualElement ve : complexMap.keySet()) {
	// ComplexElement ce = new ComplexElement();
	// ce.setVeId(ve.getId();
	// ce.setPeId(complexMap.get(ve).getId();
	// complexList.add(ce);
	// }
	// return complexList;
	// }

	protected enum XLSSheets {

		FIELDS(getProperty(FIELDS_SHEET)), TYPES(getProperty(TYPES_SHEET)), CONNECTIONS(
				getProperty(CONNECTIONS_SHEET)), STRUCTURE_MODULES(
				getProperty(STRUCTURE_SHEET)), ELEMENT_MODULES(
				getProperty(MODULES_SHEET)), CONNECTOR_NAMES(
				getProperty(CONNECTOR_NAMES_SHEET)), HISTORY(
				getProperty(HISTORY_SHEET));

		private String sheetName;

		XLSSheets(String sheetName) {
			this.sheetName = sheetName;
		}

		public String getName() {
			return sheetName;
		}

		public static XLSSheets findSheet(String name) {
			for (XLSSheets sheet : XLSSheets.values()) {
				if (sheet.getName().equals(name))
					return sheet;
			}
			logger.error("No sheet with name: " + name);
			return null;
		}

	}

	public Sheet getSheet(String sheetName)
			throws InvalidMetadataFormatException {
		Sheet sheet = workBook.getSheet(sheetName);
		if (sheet == null)
			throw new InvalidMetadataFormatException("Sheet not found!",
					XLSSheets.findSheet(sheetName));
		return sheet;
	}

}
